﻿using Dapper;
using Domain.Entity.Basic_information;
using Domain.IRepository.Basic_information;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.AccessControl;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.DependencyInjection;

namespace EFCore.Repository.Basic_information
{
    [Dependency(ServiceLifetime.Scoped)]
    public class Address_mentRepository : IAddress_mentRepository
    {
        public MyDbContext myDbContext { get; set; }

        public Address_mentRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }

        public int Count(string? Create_time,string ?value2)
        {
            string sql = "select * from [dbo].[Address_ment] where Is_del=0";
            if (Create_time != null && Create_time != "")
            {
                string[] time1 = Create_time.Split(',');
                sql += $" and [Create_time]>='{time1[0]}' and [Create_time]<='{time1[1]}'";
            }
            if (value2 != null && value2 != "")
            {
                sql += $"AND (Ar_client LIKE '%{value2}%')";
            }
            int count = myDbContext.Database.GetDbConnection().Query<Address_ment>(sql).Count();
            return count;
        }

        public List<Address_ment> GetAddress_ments(int currentPage, int PageSize)
        {
            List<Address_ment> list = myDbContext.address_Ments.Where(e => e.Is_del == 0).ToList();
            var AmList = list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
            return AmList;
        }

        //逻辑删除
        public bool DelAddress(int id)
        {
            string sql = $"update [dbo].[Address_ment] set Is_del=1 where Ar_id={id}";
            return myDbContext.Database.GetDbConnection().Execute(sql) > 0;
        }
        //添加
        public int AddAddress(Address_ment address)
        {
            string sql = $@"INSERT INTO dbo.Address_ment(Ar_type, Ar_client, Ar_contact, Ar_phone, Ar_postcode, Ar_region, Ar_adrs, Ar_remarks,Create_time,Is_del)
                        VALUES('{address.Ar_type}','{address.Ar_client}','{address.Ar_contact}','{address.Ar_phone}','{address.Ar_postcode}','{address.Ar_region}','{address.Ar_adrs}','{address.Ar_remarks}','{address.Create_time}','0')";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }
        public Address_ment GetAddress(int id)
        {
            string sql = $"SELECT * FROM dbo.Address_ment WHERE Is_del=0 AND Ar_id={id}";
            return myDbContext.Database.GetDbConnection().Query<Address_ment>(sql).FirstOrDefault();
        }
        public int UpdAddress(Address_ment address)
        {
            string sql = $@"UPDATE dbo.Address_ment SET Ar_type='{address.Ar_type}', Ar_client='{address.Ar_client}', Ar_contact='{address.Ar_contact}', Ar_phone='{address.Ar_phone}', Ar_postcode='{address.Ar_postcode}', Ar_region='{address.Ar_region}', Ar_adrs='{address.Ar_adrs}', Ar_remarks='{address.Ar_remarks}',Update_time=GETDATE()
                        WHERE Is_del=0 AND Ar_id={address.Ar_id}";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public List<Address_ment> GetAr_type(int currentPage, int PageSize)
        {
            string sql = $"SELECT * FROM dbo.Address_ment WHERE Is_del=0 AND Ar_type='105'";
            List<Address_ment> list = myDbContext.Database.GetDbConnection().Query<Address_ment>(sql).ToList();
            var address = list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
            return address;
        }

        public List<Address_ment> GetAd_type(int currentPage, int PageSize)
        {
            string sql = $"SELECT * FROM dbo.Address_ment WHERE Is_del=0 AND Ar_type='106'";
            List<Address_ment> list = myDbContext.Database.GetDbConnection().Query<Address_ment>(sql).ToList();
            var address = list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
            return address;
        }

        public List<Address_ment> Address_condition(int currentPage, int PageSize, string? Create_time, string? value2)
        {
            string sql = $"select * from [dbo].[Address_ment] where Is_del=0";
            if (Create_time != null && Create_time != "")
            {
                string[] time1 = Create_time.Split(',');
                sql += $" and [Create_time]>='{time1[0]}' and [Create_time]<='{time1[1]}'";
            }
            if (value2 != null && value2 != "")
            {
                sql += $"AND (Ar_client LIKE '%{value2}%')";
            }
            List<Address_ment> list = myDbContext.Database.GetDbConnection().Query<Address_ment>(sql).ToList();
            var address = list.Skip((currentPage - 1) * PageSize).Take(PageSize).ToList();
            return address;
        }
    }
}
